# build_extra_hint.py
# usage:
# python ./src/query/build_extra_hint.py --question "2023年第4季度每个月区县单位的按时办结率,并按照办结率从高到低排序，保留名次"

import argparse


def build_extra_hint(question):
    extra_hint_parts = []

    # 提示1：如果问题包含“平均满意率”，强调不能用 AVG，要用 SUM 计算整体比率
    if "平均满意率" in question:
        extra_hint_parts.append(
            "特别注意(平均满意率)：当计算‘平均满意率’时，禁止使用 AVG(columnX / columnY) 这种方式，会导致权重失衡。\n"
            "必须使用加权方式计算整体满意率：ROUND(1.0 * SUM(columnX) / SUM(columnY) * 100, 2)。\n"
            "正确示例：SELECT ROUND(1.0 * SUM(columnX) / SUM(columnY) * 100, 2)  "
            "FROM tableX WHERE strftime('%Y-%m', busi_time) = '2024-01';\n"
            "错误示例：SELECT AVG(columnX * 100.0 / columnY) FROM tableX; \n"
        )

    # 提示2：如果问题包含“率”（如 满意率、接通率、回复率等），提醒保留两位小数并乘以100（如果是百分比）
    if "率" in question or "百分" in question or "占比" in question:
        extra_hint_parts.append(
            "特别注意（百分比率）：涉及‘率’的计算，最终数值必须保留两位小数，使用 ROUND(..., 2) 包裹；\n"
            "若为百分比形式（如满意率、接通率），需将结果乘以 100。\n"
            "例如：SELECT ROUND(1.0 * SUM(columnX) / SUM(columnY) * 100, 2) AS FROM tableX;\n"
        )

    if "环比" in question :  
        extra_hint_parts.append(
            " 特别注意(环比):环比的计算公式是(本期数值−上期数值/上期数值),参考如下规范脚本: \n"  # 20250913_1413 ID 241+
            " SELECT ROUND((a.columnX_new - a.columnX_old)*100.0 / a.order_cnt_old,2)  \n"
            " FROM ( SELECT SUM(CASE WHEN strftime('%Y-%m', busi_time)='2024-02'  THEN columnX ELSE 0 END) AS columnX_new, \n"
            " SUM(CASE WHEN strftime('%Y-%m', busi_time)='2024-01'  THEN columnX ELSE 0 END) AS columnX_old \n"
            " FROM tableX ) AS a \n"
        )
        if "满意率" in question :  
            extra_hint_parts.append(
            " 特别注意(满意率环比): 先计算本期和上期的满意率，再进行环比公式计算\n"
            ) 

    if "百分比" in question :
        extra_hint_parts.append(
            " 特别注意（百分比）:百分比计算如果是库表各类字段与整体的占比，注意相关指标字段都增加sum()进行汇总计算，例如下面脚本columnY都应该增加sum()，可以参考如下规范样式:"
            " SELECT columnX , ROUND(SUM(columnY)*100.0/(SELECT SUM(columnY) FROM tableX WHERE date(busi_time) = '2024-01-01'),2) AS 工单占比 FROM tableX WHERE date(busi_time) = '2024-01-01' GROUP BY columnX "
        )

    if "排名" in question :
        extra_hint_parts.append(
            " 特别注意（排名）:如果问题描述涉及“排名”,主题字段和指标字段都在结果集展示，可以参考如下规范样式: \n"
            " 问题：请问xxxx年xx专题工单反馈排名前5的事项的满意率分别是多少？\n"
            " SELECT top_name , ROUND(SUM(columnY)*100.0/SUM(columnZ),2) ... \n"
        )      

    if "季度" in question :  # at 250913_1349  ID 141+ 开始启用
        extra_hint_parts.append(
            " 特别注意(季度):季度计算参考如下规范脚本: \n"
            " 一季度: strftime('%Y-%m', busi_time) IN ('2024-01', '2024-02', '2024-03') \n  "
            " 二季度: strftime('%Y-%m', busi_time) IN ('2024-04', '2024-05', '2024-06') \n  三、四季度以此类推"
        )

    if "平均处理工单时长" in question :  # at 20250918
        extra_hint_parts.append(
            " 特别注意(平均处理工单时长):计算参考如下规范脚本: \n"
            " sum(平均时长*已经办结工单)/sum(已经办结工单) \n  "
            " 如 assess_items_total_day表的计算为 sum(complete_avg_time*item_num)/sum(item_num) \n  "
        )

    if "平均处理时长" in question :  # at 20250918
        extra_hint_parts.append(
            " 特别注意(平均处理时长):计算参考如下规范脚本: \n"
            " sum(平均处理时长*工单量)/sum(工单量) \n  "
            " 如 sub_area_total_day表的计算为 sum(avg_handler_time*order_cnt)/sum(order_cnt) \n  "
        )

    if "月至" in question :  # at 250919
        extra_hint_parts.append(
            " 特别注意(跨月):跨月计算参考如下规范脚本: \n"
            " 如2025年2月至7月: date(busi_time) between '2025-02-01' and '2025-7-31') \n  "
        )
    if "分别是什么" in question :  # at 250919
        extra_hint_parts.append(
            " 特别注意(分别聚合):如果存在多结果集使用窗口函数进行归并，确保输出一个数据集 规范脚本如下： \n"
            " SELECT ... FROM (    SELECT ... ROW_NUMBER() OVER ( PARTITION BY type_name  ORDER BY ... DESC ) AS rn FROM ... WHERE col_name IN ('1', '2') ...) t WHERE rn = 1; \n"
        )
    if "排名第一" in question :  # at 250921
        extra_hint_parts.append(
            " 特别注意(排名第一):如果计算排名第一，使用窗口函数分区排序，规范脚本如下： \n"
            " WITH monthly_rank AS ( SELECT ... as monthly_total,RANK() OVER (PARTITION BY strftime('%Y-%m', busi_time) ORDER BY SUM(...) DESC) as rank FROM ... WHERE ... GROUP BY ...) SELECT ... FROM monthly_rank WHERE rank = 1 ORDER BY month \n"
        )
    if "半年每个月" in question :  # at 250921
        extra_hint_parts.append(
            " 特别注意(半年每个月):如果计算半年每个月指标情况，使用窗口函数分区排序，规范脚本如下： \n"
            " SELECT month, ... FROM (SELECT ... ,ROW_NUMBER() OVER (PARTITION BY strftime('%Y-%m', busi_time) ORDER BY SUM(...) DESC) AS rn FROM ... WHERE ... GROUP BY ...) WHERE rn = 1 ORDER BY month \n"
        )
    if "天数有多少" in question :  # at 250921
        extra_hint_parts.append(
            " 特别注意(天数有多少):如果计算满足条件的天数多少，规范脚本如下： \n"
            "  SELECT COUNT(*) FROM (SELECT ... FROM ...  WHERE ... GROUP BY ... HAVING ... > ...) \n"
        )
    # if "每个月" in question :  # at 250921
    #     extra_hint_parts.append(
    #         " 特别注意(每个月):先子查询检索各月度的记录情况，再父查询输出结果 \n"
    #         " SELECT ... FROM (SELECT ...,SUM(...t) ,ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ... DESC) AS rn FROM ... WHERE ... GROUP BY ... WHERE rn = 1 ORDER BY month\n"
    #     )
    # if "保留名次" in question :  # at 250919
    #     extra_hint_parts.append(
    #         " 特别注意(保留名次):问题如果需要保留名次，则增加一列按照要求显示排名情况 \n"
    #     )

    # 合并所有提示，若无则为空字符串
    return "\n\n".join(extra_hint_parts) if extra_hint_parts else ""


if __name__ == '__main__':
    parser = argparse.ArgumentParser(description="生成回答，提示词增强")
    parser.add_argument(
        '--question',
#         type=str,
        help='参数 --question 问题详情'
    )
    args = parser.parse_args()

    print(build_extra_hint(question=args.question))

